<?php


namespace app\index\controller;


use app\index\model\ActionRole;
use app\index\model\Product;
use app\index\model\ProductPurHistory;
use app\index\model\Purchase;
use app\index\model\PurchaseDetail;
use PhpOffice\PhpSpreadsheet\Cell\Coordinate;
use PhpOffice\PhpSpreadsheet\IOFactory;
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Worksheet\Drawing;
use think\Env;
use think\Image;

class ExcelController extends BaseController
{
    protected $path;
    protected $domain;
    protected $src=DS.'yaoxing'.DS.'excel'.DS;

    public function _initialize()
    {
        $this->path=Env::get('resource.path');
        $this->domain=Env::get('resource.image');
    }

    public function productListExcel(){
        ['action_role'=>$action_role]=ActionRole::checkUserActionRole('product_export_action');
        if($action_role===0){
            return json(['code'=>1,'msg'=>'没有权限']);
        }
        ini_set('memory_limit','1024M');
        $filed=['en_name','image','price','sea shipping'];

        $ids=input('post.ids/a');
        $query=input('post.query/a');
        $search_param = [
            'search_key' => $query['search_key'] ?? null,
            'search_value' => $query['search_value'] ?? null,
            'stock_warehouse' => $query['warehouse'] ?? null,
            'create_time' => $query['create_time'] ?? null,
            'vendor_id'=>$query['vendor_list']??null,
        ];
        if(empty($ids)) {
            if (!empty($search_param['vendor_id'])) {
                $where['vendor_id'] = ['=', $search_param['vendor_id']];
            }
            if (is_numeric($search_param['stock_warehouse'])) {
                $where['warehouse'] = ['=', $search_param['stock_warehouse']];
            }
            if (!empty($search_param['create_time'])) {
                $where['create_time'] = ['between', [$search_param['create_time'] . ' 00:00:00', $search_param['create_time'] . ' 23:59:59']];
            }
            if (!empty($search_param['search_key']) && !empty($search_param['search_value'])) {
                if ($search_param['search_key'] === 'id') {
                    $id = explode(' ', $search_param['search_value']);
                    $where[$search_param['search_key']] = ['in', $id];
                } else {
                    $where[$search_param['search_key']] = ['like', '%' . $search_param['search_value'] . '%'];
                }
            }
        }else{
            $where['id']=['in',$ids];
        }
        $Product=new Product();
        $where['del_flag']=['=',0];
        $where['status']=['=',0];
        $product_data=$Product->where($where)->field('en_name,selling_price price,image,type')->select()->toArray();
        if(empty($product_data)){
            return json(['code'=>0,'msg'=>'商品为空']);
        }
        $data=$product_data;

        $spreadsheet=new Spreadsheet();
        $spreadsheet->getProperties()
            ->setCreator("Chen")
            ->setLastModifiedBy("Chen")
            ->setTitle("Office 2007 XLSX Document")
            ->setSubject("Office 2007 XLSX Document")
            ->setDescription("Document for Office 2007 XLSX")
            ->setKeywords("office 2007 openxml php")
            ->setCategory("Product");
        $sheet = $spreadsheet->getActiveSheet();
        /*       $span=1;
                $star='A';
                $end='A';

                foreach ($filed as $key => $value) {
                    $j = Coordinate::columnIndexFromString($span);
                    if ($span === 1) {
                        $star = $j;
                    } elseif ($span === count($filed)) {
                        $end = $j;
                    }
                    $sheet->setCellValue($j . '1', $value);
                    $sheet->getColumnDimension($j)->setAutoSize(true);
                    $span++;
                }*/

//        $sheet->getStyle($star.'1:'.$end.'1')->getFont()->setBold(true)->setName('Arial')->setSize(12);
        $count = 2 ;
        $title = 1 ;
        $star='A';
        $end='A';
        foreach ($data as $key => $value) {
            if($value['type']===1){
                $value['sea shipping']=$value['price'];
                $value['sea shipping'] .= '$/' . Product::$product_type[$value['type']];
            }elseif($value['type']===2){
                $value['sea shipping']=$value['price']-10;
                $value['sea shipping'] .= '$/' . Product::$product_type[$value['type']];
            }
            $value['price'] .= '$/' . Product::$product_type[$value['type']];
            foreach ($filed as $k => $item) {// 列写入
                $j = Coordinate::stringFromColumnIndex($title);
                if($key===0){
                    if ($title === 1) {
                        $star = $j;
                    } elseif ($title === count($filed)) {
                        $end = $j;
                    }
                    $sheet->setCellValue($j . '1', $item);
                    $sheet->getColumnDimension($j)->setWidth(50);
                }
                $sheet->setCellValue($j . $count, $value[$item]);
                if($item==='image' && $value[$item]){
/*                    $thumb_image= Common::getThumbImage($value['image'], '_400X400');
                    $thumb_image = str_replace($this->domain,$this->path,$thumb_image);
                    if (is_file($thumb_image)) {
                        $sheet->getRowDimension($count)->setRowHeight(60);
                        $drawing = new Drawing();
                        $drawing->setWorksheet($sheet);
                        $drawing->setPath($thumb_image);
                        $drawing->setCoordinates($j . $count);
//                        $drawing->setOffsetX(60);
                        $drawing->setHeight(60);
                    }*/
                    $spreadsheet->getActiveSheet()->getCell($j . $count)->getHyperlink()->setUrl($value[$item]);
                }
                if ($title === count($filed)) {
                    $title = 1;
                } else {
                    $title++;
                }
            }
            if ($count === count($data) + 1) {
                $count = 2;
            } else {
                $count++;
            }
        }
        $sheet->getStyle($star.'1:'.$end.'1')->getFont()->setBold(true)->setName('Arial')->setSize(10);
        $filename = 'productlist-' . date('YmdHis').'-'.random_int(10,99);
        ob_clean();
        ob_start();
        $objWriter = IOFactory::createWriter($spreadsheet, 'Xlsx');
        $objWriter->setPreCalculateFormulas(false);
        $objWriter->save($this->path.$this->src.$filename.'.xlsx');
        $spreadsheet->disconnectWorksheets();
        unset($spreadsheet);
        ob_end_flush();
        return json(['code'=>0,'filename'=>$filename]);
    }

    public function download(){
        $filename=input('get.filename');
        $path=$this->path.$this->src.$filename.'.xlsx';
        if(!is_file($path)){
            return json(['code'=>1,'msg'=>'未找到文件']);
        }
        header("Content-type:application/octet-stream");
        header('Content-Disposition: attachment; filename='.$filename.'.xlsx');
        header('Content-Length:' . filesize($path));
        readfile($path);
        unlink($path);
    }

    public function purchasedListExcel(){
        ini_set('memory_limit','1024M');
        $filed=['purchase_id','en_name','cn_name','purchase_type_text','amount','pur_price','remark','user_name','purchase_status_text','ship_status_text','stock_status_text'];
        $label=['采购单号','英文名','中文名','采购单类型','数量','采购价','备注','用户名','采购状态','到货状态','入库状态'];

        $ids=input('post.ids/a');
        $query=input('post.query/a');
        $where['pd.del_flag']=['=',0];
        $where['p.del_flag']=['=',0];
        if(empty($ids)) {
            $search_param=[
                'search_key'=>$query['search_key']??null,
                'search_value'=>$query['search_value']??null,
                'stock_status'=>$query['stock_status']??null,
                'ship_status'=>$query['ship_status']??null,
                'vendor'=>$query['vendor']??null,
                'purchase_status'=>$query['purchase_status']??null,
                'create_time'=>$query['create_time']??null,
                'type'=>$query['type']??null,
                'priority'=>$query['priority']??null,
            ];
            if(is_numeric($search_param['stock_status'])){
                $where['pd.stock_status'] = ['=', $search_param['stock_status']];
            }
            if(is_numeric($search_param['ship_status'])){
                $where['pd.ship_status'] = ['=', $search_param['ship_status']];
            }
            if(is_numeric($search_param['purchase_status'])){
                $where['pd.status'] = ['=', $search_param['purchase_status']];
            }
            if(is_numeric($search_param['vendor'])){
                $where['pd.vendor_id'] = ['=', $search_param['vendor']];
            }
            if(!empty($search_param['create_time'])){
                $where['pd.create_time'] = ['between',[$search_param['create_time'].' 00:00:00',$search_param['create_time'].' 23:59:59']];
            }
            if(is_numeric($search_param['type'])){
                $where['p.type'] = ['=', $search_param['type']];
            }
            if(is_numeric($search_param['priority'])){
                $where['pd.priority'] = ['=', $search_param['priority']];
            }
            if (!empty($search_param['search_key']) && !empty($search_param['search_value'])) {
                if($search_param['search_key']==='purchase_id'){
                    $purchase_id=explode(' ',$search_param['search_value']);
                    $where['p.purchase_id'] = ['in', $purchase_id];
                }else {
                    $where['pd.' . $search_param['search_key']] = ['like', '%'.$search_param['search_value'].'%'];
                }
            }
        }else{
            $where['pd.id']=['in',$ids];
        }
        $PurchaseDetailClass=new PurchaseDetail();
        $obj=$PurchaseDetailClass->alias('pd')
            ->join('purchase p','pd.pur_id=p.id','left')
//            ->join('vendor v','pd.vendor_id=v.id','left')
            ->join('user u','pd.create_user=u.id','left')
            ->where($where)
            ->field('p.purchase_id,pd.en_name,pd.cn_name,pd.remark,pd.amount,pd.pur_price,pd.status,pd.ship_status,pd.stock_status,pd.priority,p.type purchase_type,u.user_name')
            ->select();
        $data=$obj->toArray();
        if(empty($data)){
            return json(['code'=>0,'msg'=>'采购单为空']);
        }

        $spreadsheet=new Spreadsheet();
        $spreadsheet->getProperties()
            ->setCreator("Chen")
            ->setLastModifiedBy("Chen")
            ->setTitle("Office 2007 XLSX Document")
            ->setSubject("Office 2007 XLSX Document")
            ->setDescription("Document for Office 2007 XLSX")
            ->setKeywords("office 2007 openxml php")
            ->setCategory("Product");
        $sheet = $spreadsheet->getActiveSheet();
        $count = 2 ;
        $title = 1 ;
        $star='A';
        $end='A';
        foreach ($data as $key => $value) {
            $value['purchase_type_text']=$PurchaseDetailClass->type_list[$value['purchase_type']]['label'];
            $value['purchase_status_text']=$PurchaseDetailClass->purchase_status[$value['status']];
            $value['ship_status_text']=$PurchaseDetailClass->ship_status[$value['ship_status']];
            $value['stock_status_text']=$PurchaseDetailClass->stock_status[$value['stock_status']];
            foreach ($filed as $k => $item) {// 列写入
                $j = Coordinate::stringFromColumnIndex($title);
                if($key===0){
                    if ($title === 1) {
                        $star = $j;
                    } elseif ($title === count($filed)) {
                        $end = $j;
                    }
                    $sheet->setCellValue($j . '1', $label[$k]);
                    $sheet->getColumnDimension($j)->setWidth(30);
                }
                $sheet->setCellValue($j . $count, $value[$item]);

                if ($title === count($filed)) {
                    $title = 1;
                } else {
                    $title++;
                }
            }
            if ($count === count($data) + 1) {
                $count = 2;
            } else {
                $count++;
            }
        }
        $sheet->getStyle($star.'1:'.$end.'1')->getFont()->setBold(true)->setName('Arial')->setSize(10);
        $filename = 'purchaselist-' . date('YmdHis').'-'.random_int(10,99);
        ob_clean();
        ob_start();
        $objWriter = IOFactory::createWriter($spreadsheet, 'Xlsx');
        $objWriter->setPreCalculateFormulas(false);
        $objWriter->save($this->path.$this->src.$filename.'.xlsx');
        $spreadsheet->disconnectWorksheets();
        unset($spreadsheet);
        ob_end_flush();
        return json(['code'=>0,'filename'=>$filename]);
    }

    public function productHistoryListExcel(){
        ini_set('memory_limit','1024M');
        $filed=['id','en_name','cn_name','pur_price','vendor','type_text','amount','product_type_text','user_name','create_time'];
        $label=['id','英文名','中文名','采购单价','供应商','操作','数量','计量单位','用户','操作时间'];

//        $ids=input('post.ids/a');
        $query=input('post.query/a');
        $search_param = [
            'search_key' => $query['search_key'] ?? null,
            'search_value' => $query['search_value'] ?? null,
            'stock_warehouse' => $query['warehouse'] ?? null,
            'type' => $query['type'] ?? null,
            'vendor_id' => $query['vendor_id'] ?? null,
            'create_time' => $query['create_time'] ?? null,
            'group_id' => $query['group_id'] ?? null,
            'user_id' => $query['user_id'] ?? null,
        ];
        ['status'=>$status,'result'=>$result]=ProductPurHistory::getStockHistoryList($search_param,[]);
        if($status===false){
            return json(['code'=>0,'msg'=>'历史为空']);
        }
        $data=$result['product'];
        $spreadsheet=new Spreadsheet();
        $spreadsheet->getProperties()
            ->setCreator("Chen")
            ->setLastModifiedBy("Chen")
            ->setTitle("Office 2007 XLSX Document")
            ->setSubject("Office 2007 XLSX Document")
            ->setDescription("Document for Office 2007 XLSX")
            ->setKeywords("office 2007 openxml php")
            ->setCategory("Product");
        $sheet = $spreadsheet->getActiveSheet();
        $count = 2 ;
        $title = 1 ;
        $star='A';
        $end='A';
        foreach ($data as $key => $value) {
            foreach ($filed as $k => $item) {// 列写入
                $j = Coordinate::stringFromColumnIndex($title);
                if($key===0){
                    if ($title === 1) {
                        $star = $j;
                    } elseif ($title === count($filed)) {
                        $end = $j;
                    }
                    $sheet->setCellValue($j . '1', $label[$k]);
                    $sheet->getColumnDimension($j)->setWidth(30);
                }
                $sheet->setCellValue($j . $count, $value[$item]);

                if ($title === count($filed)) {
                    $title = 1;
                } else {
                    $title++;
                }
            }
            if ($count === count($data) + 1) {
                $count = 2;
            } else {
                $count++;
            }
        }
        $sheet->getStyle($star.'1:'.$end.'1')->getFont()->setBold(true)->setName('Arial')->setSize(10);
        $filename = 'product_history_list-' . date('YmdHis').'-'.random_int(10,99);
        ob_clean();
        ob_start();
        $objWriter = IOFactory::createWriter($spreadsheet, 'Xlsx');
        $objWriter->setPreCalculateFormulas(false);
        $objWriter->save($this->path.$this->src.$filename.'.xlsx');
        $spreadsheet->disconnectWorksheets();
        unset($spreadsheet);
        ob_end_flush();
        return json(['code'=>0,'filename'=>$filename]);
    }
}